package com.example.jpademo.biz.service;

import cn.hutool.core.util.ReflectUtil;
import com.example.jpademo.biz.dao.AllowanceMapper;
import com.example.jpademo.biz.entity.AllowancePO;
import com.example.jpademo.biz.vo.AllowanceSearchVO;
import com.example.jpademo.common.baseEntity.annation.SearchReflection;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.mysql.cj.jdbc.JdbcStatement;
import org.apache.tomcat.util.buf.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Service;

import javax.persistence.Column;
import javax.persistence.Table;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicLong;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * @author guoxh
 * @Date 2020-08-31 10:18:44
 */
@Service
public class AllowanceService {
    private final Logger logger = LoggerFactory.getLogger(AllowanceService.class);

    @Autowired
    private AllowanceMapper allowanceMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void insertData(){
        for(int i = 0;i<500;i++){
            AllowancePO po = new AllowancePO();
            po.setId(UUID.randomUUID().toString());
            po.setName("name"+i);
            po.setType("type"+i);
            po.setTenantId("tenant");
            po.setDr(0);
            po.setTs(LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE_TIME));

            allowanceMapper.insertData(po);
        }
    }

    public List<AllowancePO> findAll(AllowanceSearchVO po){
        return allowanceMapper.findAll();
    }

    public void queryPage(){
        String dataSql = "select id,name,type,tenant_id,ts,dr from allowances limit 0,20";
        String countSql = "select count(1) from allowances";

        List<AllowancePO> list = jdbcTemplate.query(dataSql,new Object[]{},new BeanPropertyRowMapper<AllowancePO>(AllowancePO.class));
        Long count = jdbcTemplate.queryForObject(countSql,Long.class);
        PageImpl<AllowancePO> page = new PageImpl<>(list,PageRequest.of(0,20),count);
        System.out.println(count);

    }

    //https://blog.csdn.net/weixin_34289454/article/details/92531109
    public void queryPageHelper(){
        String dataSql = "select id,name,type,tenant_id,ts,dr from allowances";
        PageHelper.startPage(0,20);
        List<AllowancePO> list = jdbcTemplate.query(dataSql,new Object[]{},new BeanPropertyRowMapper<AllowancePO>(AllowancePO.class));
        PageInfo<AllowancePO> pageInfo = new PageInfo<>(list);
        System.out.println(pageInfo.getSize());
    }

    public List<String> generateIds(int size){
        List<String> list = new ArrayList<>(size);
        IntStream.range(0,size).forEach( i -> list.add(i+"1"));
        return list;
    }

    public String loadSearchAnnontation(AllowanceSearchVO allowanceSearchVO) throws IllegalAccessException, NoSuchFieldException {

        StringBuffer sqlBbuffer = new StringBuffer("select * from %s");
        String tableName = "";
        Field[] fields = AllowanceSearchVO.class.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields).filter(field -> field.getAnnotation(SearchReflection.class) != null).collect(Collectors.toList());

        for(Field field : fieldList){
            logger.info("拼装拼装field为{}的sql开始",field.getName());
            //获取注解上的内容
            field.setAccessible(true);
            String reflectionName = field.getAnnotation(SearchReflection.class).reflectionName();
            Class<?> reflectionClazz = field.getAnnotation(SearchReflection.class).reflectionClzz();
            //获取searchVO上属性的值
            Object value = field.get(allowanceSearchVO);
            //根据注解信息拿到PO类的表名和field对应的列名
            Field originField  = ReflectUtil.getField(reflectionClazz,reflectionName);
            String columnName = originField.getAnnotation(Column.class).name();
            tableName = reflectionClazz.getAnnotation(Table.class).name();
            logger.info("实体类对应的表名为{},列名为{}",tableName,columnName);
            //生成临时表表名
            String tmpTaleName = "tmp" + this.getIncrId();
            logger.info("生成的临时表表名为{}",tmpTaleName);
            //创建临时表 && 插入数据到临时表
            if(Objects.nonNull(value) && field.getType() == List.class){
                if(originField.getType() == String.class) {
                    strList2sql(tmpTaleName, (List<String>) value);
                }else{
                    objList2sql(tmpTaleName, (List<Object>) value);
                }
                //拼装SQL
                String querySql = String.format(" inner join %s on %s.value = %s.%s",tmpTaleName,tmpTaleName,tableName,columnName);
                logger.info("拼装field为{}的sql结束,生成的sql是{}",field.getName(),querySql);
                sqlBbuffer.append(querySql);
            }
        }

        //输出完整SQL
        String result = String.format(sqlBbuffer.toString(),tableName);
        System.out.println(result);
        return result;
    }

    /**
     * List<String>拼装插入临时表SQL
     * @param tableName
     * @param values
     */
    private void strList2sql(String tableName,List<String> values){
        String ddl = String.format("create table if not exists %s (value varchar(256))",tableName);
        String dml = String.format("insert into %s values ('" + values.stream().collect(Collectors.joining("'),('")) + "')",tableName);
        logger.info("String对象生成的建表语句是{}",ddl);
        logger.info("String对象生成的插入语句是{}",dml);
        jdbcTemplate.batchUpdate(ddl,dml);
    }

    /**
     * List<Object>拼装插入临时表SQL 一般为 Integer,Double等不要进行''进行拼接的字段
     * @param tableName
     * @param values
     */
    private void objList2sql(String tableName,List<Object> values){
        List<String> list = generateIds(10);
        String ddl = String.format("create table if not exists %s (value varchar(256))",tableName);
        String dml = String.format("insert into %s values (" + values.stream().map(Object::toString).collect(Collectors.joining("),(")) + ")",tableName);
        logger.info("Object对象生成的建表语句是{}",ddl);
        logger.info("Object对象生成的插入语句是{}",dml);
        jdbcTemplate.batchUpdate(ddl,dml);
    }

    private Long getIncrId(){
        AtomicLong result = new AtomicLong(0L);
        String sql  = "insert into tmptable(value) select 1";
        jdbcTemplate.execute(sql, (PreparedStatementCallback<Long>) (preparedStatement)->{
            preparedStatement.execute(sql,Statement.RETURN_GENERATED_KEYS);
            ResultSet resultSet = preparedStatement.getGeneratedKeys();

            while (resultSet.next()){
                result.set(resultSet.getLong(1));
            }
            logger.info("查找tmptable的主键最大值是{}",result.get());
            return null;
        });
        return result.get();
    }



    @Deprecated
    private String list2String(){
        List<String> list = generateIds(10);
        String ids = StringUtils.join(list, '\n');
        return ids;
    }
    /**
     * mysql LOAD DATA LOCAL INFILE
     * MySQL8 默认关闭,详情可见 http://www.1024sky.cn/blog/article/895
     * @return
     */
    @Deprecated
    private void prepare(String loadSql, InputStream is) throws SQLException {
        Connection conn = jdbcTemplate.getDataSource().getConnection();
        PreparedStatement psmt = conn.prepareStatement(loadSql);
        int result = 0;
        if(psmt.isWrapperFor(JdbcStatement.class)){
            JdbcStatement statement = psmt.unwrap(JdbcStatement.class);
            statement.setLocalInfileInputStream(is);
        }
        result = psmt.executeUpdate();
    }


}
